/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.AdlPc;
import me.zhengjie.business.domain.Zhsq;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2023-10-28
**/
public interface LargeScreenRepository extends JpaRepository<Zhsq, Long>, JpaSpecificationExecutor<Zhsq> {

    /**
     * 每月各区申请人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm'),count(*) from bis_zhsq t   " +
            "where t.bis_zhsq_zhlb=?3 " +
            "and t.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "and t.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            "group by t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm') " +
            "order by t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm')" ,nativeQuery = true)
    List<Object> queryLeft1(String starttime, String endtime, String zhlb);

    @Query(value = "select t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm'),count(*) from bis_zhsq t   " +
            "where t.bis_zhsq_zhlb=?2 " +
            "and to_char(t.lrsj, 'yyyy-mm')  = ?1 " +
            "group by t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm') " +
            "order by t.bis_zhsq_hldz_ssqx, to_char(t.lrsj, 'yyyy-mm')" ,nativeQuery = true)
    List<Object> queryLeft11(String yf, String zhlb);
    /**
     * 每月申请人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(t.lrsj, 'yyyy-mm'),count(*) from bis_zhsq t  " +
            "where t.bis_zhsq_zhlb=?3 " +
            "and t.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "and t.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            "group by to_char(t.lrsj, 'yyyy-mm') " +
            "order by to_char(t.lrsj, 'yyyy-mm')" ,nativeQuery = true)
    List<Object> queryLeft2(String starttime, String endtime, String zhlb);

    /**
     * 每月职工、居民的申请人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(t.lrsj, 'yyyy-mm') , t.bis_zhsq_cblx, count(*) from bis_zhsq t  " +
            "where t.bis_zhsq_zhlb=?3 " +
            "and t.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "and t.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            "group by to_char(t.lrsj, 'yyyy-mm') , t.bis_zhsq_cblx " +
            "order by to_char(t.lrsj, 'yyyy-mm') , t.bis_zhsq_cblx" ,nativeQuery = true)
    List<Object> queryLeft3(String starttime, String endtime, String zhlb);

    /**
     * 每月已签约人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(t2.lrsj, 'yyyy-mm'), count(1), to_char(t2.lrsj, 'mm')||'月' " +
            "  from bis_zhsq t1, BIS_QY t2  " +
            " where t1.bis_zhsq_id = t2.bis_qy_zhsq_id  " +
            "   and t2.bis_gsgl_iszf = '1' " +
            "   and t1.bis_zhsq_zhlb = ?3 " +
            "   and t1.bis_zhsq_zt in ('13', '90', '91') " +
            "  and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "  and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            " group by to_char(t2.lrsj, 'yyyy-mm'), to_char(t2.lrsj, 'mm') " +
            " order by to_char(t2.lrsj, 'yyyy-mm')" ,nativeQuery = true)
    List<Object> queryLeft4(String starttime, String endtime, String zhlb);

    /**
     * 每月已服务人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select substr(t3.bis_order_hlrq, 0, 7) ,count(distinct t.bis_zhsq_snry_sfz), substr(t3.bis_order_hlrq, 6, 2)||'月' " +
            "  from bis_zhsq t " +
            "  left join bis_order t3 on t.bis_zhsq_id = t3.bis_order_zhid " +
            " where t.bis_zhsq_zhlb = ?3 " +
            "  and t.bis_zhsq_zt in ('13', '90', '91') " +
            "  and t3.bis_order_hlrq>=?1 " +
            "  and t3.bis_order_hlrq<=?2 " +
            "  and t3.bis_order_zt in ('1', '3', '2', '9') " +
            " group by substr(t3.bis_order_hlrq, 0, 7), substr(t3.bis_order_hlrq, 6, 2) " +
            " order by substr(t3.bis_order_hlrq, 0, 7) " ,nativeQuery = true)
    List<Object> queryLeft5(String starttime, String endtime, String zhlb);

    /**
     * 每月服务人次
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select substr(t3.bis_order_hlrq, 0, 7) ,count(t.bis_zhsq_id), substr(t3.bis_order_hlrq, 6, 2)||'月' " +
            "  from bis_zhsq t " +
            "  left join bis_order t3 on t.bis_zhsq_id = t3.bis_order_zhid " +
            " where t.bis_zhsq_zhlb = '03' " +
            "  and t.bis_zhsq_zt in ('13', '90', '91') " +
            "  and t3.bis_order_hlrq>=?1 " +
            "  and t3.bis_order_hlrq<=?2 " +
            "  and t3.bis_order_zt in ('1', '3', '2', '9') " +
            " group by substr(t3.bis_order_hlrq, 0, 7), substr(t3.bis_order_hlrq, 6, 2) " +
            " order by substr(t3.bis_order_hlrq, 0, 7) " ,nativeQuery = true)
    List<Object> queryCenter3(String starttime, String endtime, String zhlb);


    /**
     * 每月服务人次
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(AKC192, 'yyyy-mm') ,count(*), substr(to_char(AKC192, 'yyyy-mm-dd'), 6, 2)||'月' from kc21 " +
            "where aka130='医疗照护' " +
            "and AKC192 >= to_date(?1,'yyyy-mm-dd') " +
            "and AKC192 <= to_date(?2,'yyyy-mm-dd') " +
            "group by to_char(AKC192, 'yyyy-mm'), substr(to_char(AKC192, 'yyyy-mm-dd'), 6, 2) " +
            "order by to_char(AKC192, 'yyyy-mm') " ,nativeQuery = true)
    List<Object> queryCenter31(String starttime, String endtime, String zhlb);

    /**
     * 每月评估人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(t2.lrsj, 'yyyy-mm'), count(1) " +
            "  from bis_zhsq t1, bis_zhsq_history t2 " +
            " where t1.bis_zhsq_id = t2.zhsq_id " +
            "   and t1.bis_zhsq_zhlb = ?3 " +
            "   and t2.type = '02' " +
            "   and t2.result = '通过' " +
            "  and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "  and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            " group by to_char(t2.lrsj, 'yyyy-mm') " +
            " order by to_char(t2.lrsj, 'yyyy-mm') " ,nativeQuery = true)
    List<Object> queryRigth1(String starttime, String endtime, String zhlb);

    /**
     * 每月复核人数
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(t2.lrsj, 'yyyy-mm'), count(1) " +
            "  from bis_zhsq t1, bis_zhsq_history t2 " +
            " where t1.bis_zhsq_id = t2.zhsq_id " +
            "   and t1.bis_zhsq_zhlb = ?3 " +
            "   and t2.type = '04' " +
            "   and t2.result = '通过' " +
            "  and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') " +
            "  and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') " +
            " group by to_char(t2.lrsj, 'yyyy-mm') " +
            " order by to_char(t2.lrsj, 'yyyy-mm') " ,nativeQuery = true)
    List<Object> queryRigth2(String starttime, String endtime, String zhlb);

    /**
     * 机构已享受待遇各失能等级人员数量
     * @return /
     */
    @Query(value = "select t.bis_zhsq_sndj, count(*) from bis_zhsq t  " +
            "where t.bis_zhsq_zhlb=?1 " +
            "  and t.bis_zhsq_zt = '05' " +
            "group by t.bis_zhsq_sndj " +
            "order by t.bis_zhsq_sndj " ,nativeQuery = true)
    List<Object> queryRigth3(String zhlb);

    /**
     * 机构已享受待遇各参保类型人员数量
     * @return /
     */
    @Query(value = "select t.bis_zhsq_cblx, count(*) from bis_zhsq t  " +
            "where t.bis_zhsq_zhlb='01' " +
            "  and t.bis_zhsq_zt = '05' " +
            "group by t.bis_zhsq_cblx " +
            "order by t.bis_zhsq_cblx " ,nativeQuery = true)
    List<Object> queryRigth4(String zhlb);
    /**
     * 居家已享受待遇各参保类型人员数量
     * @return /
     */
    @Query(value = "select t.bis_zhsq_cblx,count(distinct t.bis_zhsq_snry_sfz) " +
            "  from bis_zhsq t " +
            "  left join bis_order t3 " +
            "    on t.bis_zhsq_id = t3.bis_order_zhid " +
            " where t.bis_zhsq_zhlb = '03' " +
            "   and t.bis_zhsq_zt in ('13', '90', '91') " +
            "   and t3.bis_order_hlrq >= ?1 " +
            "   and t3.bis_order_hlrq <= ?2 " +
            "   and t3.bis_order_zt in ('1', '3', '2', '9') " +
            "   group by t.bis_zhsq_cblx " +
            "   order by t.bis_zhsq_cblx " ,nativeQuery = true)
    List<Object> queryRigth44(String starttime, String endtime);

    /**
     * 概况
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = " select " +
            " (select count(1) from bis_zhsq t  where t.bis_zhsq_zhlb=?3 and t.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss') and t.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss')  ) sqcnt, " +
            " (select count(1) from bis_zhsq t1, bis_zhsq_history t2  where t1.bis_zhsq_id = t2.zhsq_id    and t1.bis_zhsq_zhlb = ?3    and t2.type = '02'    and t2.result = '通过'   and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss')   and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') ) pgcnt, " +
            " (select count(1) from bis_zhsq t1, bis_zhsq_history t2  where t1.bis_zhsq_id = t2.zhsq_id    and t1.bis_zhsq_zhlb = ?3    and t2.type = '04'    and t2.result = '通过'   and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss')   and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss') ) fhcnt, " +
            " (select count(1) from bis_zhsq t1, BIS_QY t2   where t1.bis_zhsq_id = t2.bis_qy_zhsq_id     and t2.bis_gsgl_iszf = '1'    and t1.bis_zhsq_zhlb = ?3    and t1.bis_zhsq_zt in ('13', '90', '91')   and t2.lrsj>=to_date(?1, 'yyyy-mm-dd hh24:mi:ss')   and t2.lrsj<=to_date(?2, 'yyyy-mm-dd hh24:mi:ss')) qycnt,  " +
            " (select count(distinct t.bis_zhsq_snry_sfz) from bis_zhsq t   left join bis_order t3 on t.bis_zhsq_id = t3.bis_order_zhid  where t.bis_zhsq_zhlb = ?3   and t.bis_zhsq_zt in ('13', '90', '91')   and t3.bis_order_hlrq>=?1   and t3.bis_order_hlrq<=?2   and t3.bis_order_zt in ('1', '3', '2', '9')) fwrccnt,  " +
            " (select count(t.bis_zhsq_id) from bis_zhsq t   left join bis_order t3 on t.bis_zhsq_id = t3.bis_order_zhid  where t.bis_zhsq_zhlb = ?3   and t.bis_zhsq_zt in ('13', '90', '91')   and t3.bis_order_hlrq>=?1   and t3.bis_order_hlrq<=?2   and t3.bis_order_zt in ('1', '3', '2', '9')) fwrscnt, " +
            "  (select count( t.bis_zhsq_id) from bis_zhsq t  where t.bis_zhsq_zhlb = ?3   and t.bis_zhsq_zt = '05'    ) jgcnt " +
            "from dual " ,nativeQuery = true)
    List<Object> queryTop(String starttime, String endtime, String zhlb);

    /**
     * 查询居家
     * @param jg /
     * @return /
     */
    @Query(value = "select t.bis_gsgl_id, " +
            "t.bis_gsgl_qymc, " +
            "t.bis_gsgl_yljgdm, " +
            "t.bis_gsgl_jd, " +
            "t.bis_gsgl_wd " +
            "from bis_gsgl t " +
            "where t.bis_gsgl_chjgddfwlx = '01' " +
            "and (t.bis_gsgl_qymc like concat(concat('%', ?1),'%') or t.bis_gsgl_yljgdm like concat(concat('%', ?1),'%'))" ,nativeQuery = true)
    List<Object> queryHomeGs(String jg);

    /**
     * 查询机构
     * @param jg /
     * @return /
     */
    @Query(value = "select t.bis_gsgl_id, " +
            "t.bis_gsgl_qymc, " +
            "t.bis_gsgl_yljgdm, " +
            "t.bis_gsgl_jd, " +
            "t.bis_gsgl_wd " +
            "from bis_gsgl t " +
            "where t.bis_gsgl_chjgddfwlx = '02' " +
            "and (t.bis_gsgl_qymc like concat(concat('%', ?1),'%') or t.bis_gsgl_yljgdm like concat(concat('%', ?1),'%'))" ,nativeQuery = true)
    List<Object> queryOrgGs(String jg);


    /**
     * 查询每月统筹金额
     * @param starttime /
     * @param endtime /
     * @return /
     */
    @Query(value = "select to_char(to_date(aae002,'yyyy-mm'), 'yyyy-mm'), CJT100, sum(AKB068) from kb03 t " +
            "where t.aae002 >= ?1 and t.aae002 <= ?2 " +
            "and t.aae016 = ?3 " +
            "group by to_char(to_date(aae002,'yyyy-mm'), 'yyyy-mm'), CJT100 " +
            "order by to_char(to_date(aae002,'yyyy-mm'), 'yyyy-mm'), CJT100 " ,nativeQuery = true)
    List<Object> queryAmount(String starttime, String endtime, String zhlb);
}